/**
 *
 * @author Duc On (ductuanon@hotmail.com)
 */
package com.querie.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.List;

import com.querie.parser.ParserFactory;
import com.querie.parser.Query;
import com.querie.pojo.Table;
import com.querie.pojo.Column;
import com.querie.pojo.WCondition;
import com.querie.collab.ItemSimilarity;
import com.querie.util.QryParameters;

public class QueryFragmentFactory_1 implements Runnable {

    private Connection mySqlConnection = null;
    private ParserFactory paserFactory = null;
    private String filter = null;

    public void setFilter(String filter) {
        this.filter = filter;
    }

    public QueryFragmentFactory_1() throws SQLException {
        mySqlConnection = DBMSConnection.getConnection();
        paserFactory = com.querie.parser.ParserFactory.getInstance();
    }

    public void run() {
        try {
            processQueriesFromDB();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }

    /* Part of Backend engine
     * This processes all the queries in database at the point this is run.
    Converts all queries into items and finds the top k most similar items for each item.
     *
     */
    public void processQueriesFromDB() throws Exception {
        String qString = null;
        String qID = null;
        boolean parsedQuery = false;
        if (mySqlConnection != null) {
            clean(false);
            Statement stmt = mySqlConnection.createStatement();
            String sql = "SELECT * FROM sessionQueries";
            if (filter != null) {
                sql = sql + filter;
            }

            System.out.println(sql);
            ResultSet resultSet = stmt.executeQuery(sql);

            while (resultSet.next() == true) {
                qString = resultSet.getString(2);
                qID = resultSet.getString(3);
                parsedQuery = checkIfQueryAlreadyParsed(qID);
                System.out.println("Query:" + qString + " ID:" + qID + "Already parsed:" + parsedQuery);
                if(parsedQuery){
                    continue;
                }
                Query queryObject = new Query(resultSet.getString(2));
                queryObject.setQueryID(resultSet.getString(3));
                paserFactory.parse(queryObject, mySqlConnection);
                try {
                    addQryFragmentsToDB(queryObject);
                    addQryTablesToDB(queryObject);
                    addQryColumnsToDB(queryObject);
                    addQryWhereToDB(queryObject);
                } catch (SQLException ex) {
                    System.out.println("processQueriesFromDB: " + ex.getSQLState());
                    if (ex.getSQLState().equals("23000")) {
                        // If queryid is already in the table. Do not reinsert. Skip
                        continue;
                    }
                }
            }

            DBMSConnection.safeClose(resultSet, stmt, mySqlConnection);
        }
        

    }

    /* Adds the query fragments in the POJO object to the database.
     *
     */
    private boolean checkIfQueryAlreadyParsed(String qid) throws Exception {
        int cnt = 0;
        PreparedStatement stmt = mySqlConnection.prepareStatement(
                "select count(queryId) from qryFragments where queryId = ?");

        stmt.setString(1, qid);
        ResultSet rs = stmt.executeQuery();
        if(rs.next()){
            cnt = rs.getInt(1);
        }

        DBMSConnection.safeClose(rs, stmt, null);
        if(cnt > 0) return true;
        else
            return false;
    }

    /* Adds the query fragments in the POJO object to the database.
     *
     */
    private void addQryFragmentsToDB(Query queryObject) throws Exception {
        PreparedStatement stmt = mySqlConnection.prepareStatement(
                "INSERT INTO qryFragments (queryId, columnFrag, tableFrag, joinFrag, " + "groupFrag, havingFrag, orderFrag) VALUES (?, ?, ?, ?, ?, ?, ?)");

        stmt.setString(1, queryObject.getQueryID());
        stmt.setString(2, queryObject.getFragments().getColumnFrag());
        stmt.setString(3, queryObject.getFragments().getTableFrag());
        stmt.setString(4, queryObject.getFragments().getJoinFrag());
        stmt.setString(5, queryObject.getFragments().getGroupFrag());
        stmt.setString(6, queryObject.getFragments().getHavingFrag());
        stmt.setString(7, queryObject.getFragments().getOrderFrag());
        stmt.executeUpdate();

        DBMSConnection.safeClose(null, stmt, null);
    }

    /* Add the tables as items and relates to the query into the database
     *
     */
    private void addQryTablesToDB(Query queryObject) throws Exception {
        List<Table> listOfTables = queryObject.getTables();
        ResultSet rs1 = null;
        try {
            if (listOfTables != null) {
                PreparedStatement stmt1 = mySqlConnection.prepareStatement(
                        "SELECT itemId FROM collabItems WHERE itemName = ? AND isTable = 'Y'");
                PreparedStatement stmt2 = mySqlConnection.prepareStatement(
                        "INSERT INTO collabItems (itemName, isTable) VALUES (?, 'Y')");
                PreparedStatement stmt3 = mySqlConnection.prepareStatement(
                        "INSERT INTO qryItems (queryId, itemId) VALUES (?, ?)");
                stmt3.setString(1, queryObject.getQueryID());
                String tableId;

                for (int i = 0; i < listOfTables.size(); i++) {
                    //System.out.println("Inserting table, inside list");
                    if ((tableId = listOfTables.get(i).getTableID()) != null) {
                        stmt1.setString(1, tableId);
                        rs1 = stmt1.executeQuery();
                        if (rs1.first() == false) {        // Table does not exist yet
                            stmt2.setString(1, tableId);
                            stmt2.executeUpdate();
                            DBMSConnection.safeClose(rs1, null, null);
                            rs1 = stmt1.executeQuery();
                            if (rs1.first() == false) {    // Couldn't get the last inserted itemId
                                throw new SQLException("Failed to get the last inserted itemId.");
                            }
                        }

                        stmt3.setString(2, rs1.getString("itemId"));
                        stmt3.executeUpdate();
                        DBMSConnection.safeClose(rs1, null, null);
                    }
                }

                DBMSConnection.safeClose(null, stmt3, null);
                DBMSConnection.safeClose(null, stmt2, null);
                DBMSConnection.safeClose(null, stmt1, null);
            }
        } finally {
            DBMSConnection.safeClose(rs1, null, null);
        }
    }

    /* Add the columns as items and relates to the query into the database
     *
     */
    private void addQryColumnsToDB(Query queryObject) throws Exception {
        ResultSet rs1 = null;
        try {
            List<Column> listOfColumns = queryObject.getColumns();


            if (listOfColumns != null) {
                PreparedStatement stmt1 = mySqlConnection.prepareStatement(
                        "SELECT itemId FROM collabItems WHERE itemName = ? AND isColumn = 'Y'");
                PreparedStatement stmt2 = mySqlConnection.prepareStatement(
                        "INSERT INTO collabItems (itemName, isColumn) VALUES (?, 'Y')");
                PreparedStatement stmt3 = mySqlConnection.prepareStatement(
                        "INSERT INTO qryItems (queryId, itemId) VALUES (?, ?)");
                stmt3.setString(1, queryObject.getQueryID());
                String columnId = null;

                for (int i = 0; i < listOfColumns.size(); i++) {
                    columnId = listOfColumns.get(i).getColumnID();
                    if (columnId == null) {
                        columnId = listOfColumns.get(i).getColumnName();
                    }
                    //System.out.println("Inserting column, inside list " + columnId);
                    if (columnId != null) {
                        stmt1.setString(1, columnId);
                        rs1 = stmt1.executeQuery();
                        if (rs1.first() == false) {        // Column does not exist yet
                            stmt2.setString(1, columnId);
                            stmt2.executeUpdate();
                            DBMSConnection.safeClose(rs1, null, null);
                            rs1 = stmt1.executeQuery();
                            if (rs1.first() == false) {    // Couldn't get the last inserted itemId
                                throw new SQLException("Failed to get the last inserted itemId.");
                            }

                        }

                        stmt3.setString(2, rs1.getString("itemId"));
                        stmt3.executeUpdate();
                        DBMSConnection.safeClose(rs1, null, null);
                    }
                }

                DBMSConnection.safeClose(null, stmt3, null);
                DBMSConnection.safeClose(null, stmt2, null);
                DBMSConnection.safeClose(null, stmt1, null);
            }
        } finally {
            DBMSConnection.safeClose(rs1, null, null);
        }
    }

    /* Add the where clauses as items and relates to the query into the database
     *
     */
    public void addQryWhereToDB(Query queryObject) throws Exception {
        List<WCondition> listOfConditions = queryObject.getConditions();
        ResultSet rs1 = null;

        try {

            if (listOfConditions != null) {
                PreparedStatement stmt1 = mySqlConnection.prepareStatement(
                        "SELECT itemId FROM collabItems WHERE itemName = ? AND isWhere = 'Y'");
                PreparedStatement stmt2 = mySqlConnection.prepareStatement(
                        "INSERT INTO collabItems (itemName, isWhere) VALUES (?, 'Y')");
                PreparedStatement stmt3 = mySqlConnection.prepareStatement(
                        "INSERT INTO qryItems (queryId, itemId) VALUES (?, ?)");
                stmt3.setString(1, queryObject.getQueryID());
                String whereId;

                for (int i = 0; i < listOfConditions.size(); i++) {
                    //System.out.println("Inserting where, inside list");

                    if ((whereId = listOfConditions.get(i).getConvCondition()) != null) {
                        stmt1.setString(1, whereId);
                        rs1 = stmt1.executeQuery();
                        if (rs1.first() == false) {        // Condition does not exist yet
                            stmt2.setString(1, whereId);
                            stmt2.executeUpdate();
                            DBMSConnection.safeClose(rs1, null, null);
                            rs1 = stmt1.executeQuery();
                            if (rs1.first() == false) {    // Couldn't get the last inserted itemId
                                throw new SQLException("Failed to get the last inserted itemId.");
                            }
                        }

                        stmt3.setString(2, rs1.getString("itemId"));
                        stmt3.executeUpdate();
                        DBMSConnection.safeClose(rs1, null, null);
                    }
                }

                DBMSConnection.safeClose(null, stmt3, null);
                DBMSConnection.safeClose(null, stmt2, null);
                DBMSConnection.safeClose(null, stmt1, null);
            }
        } finally {
            DBMSConnection.safeClose(rs1, null, null);
        }
    }

    /* Processes the query of the active user and converts them to items and adds them to
     * database as items and relates to the query
     */
    public Query processQueriesForSession(int queryId, Connection con) throws Exception {
        Query queryObject = null;
        boolean parsedQuery = false;
        String qString = null;
        String qID = null;

        String sql = "SELECT * FROM sessionQueries where queryid = ?";
        PreparedStatement stmt1 = con.prepareStatement(sql);
        stmt1.setInt(1, queryId);
        ResultSet resultSet = stmt1.executeQuery();

        while (resultSet.next() == true) {
            qString = resultSet.getString(2);
                qID = resultSet.getString(3);
            queryObject = new Query( qString);
            queryObject.setQueryID(qID);
            parsedQuery = checkIfQueryAlreadyParsed(qID);
                System.out.println("Query:" + qString + " ID:" + qID + "Already parsed:" + parsedQuery);
                if(parsedQuery){
                    continue;
                }
            paserFactory.parse(queryObject, con);
            System.out.println("Single query processing");
            try {
                addQryFragmentsToDB(queryObject);
                addQryTablesToDB(queryObject);
                addQryColumnsToDB(queryObject);
                addQryWhereToDB(queryObject);
            } catch (SQLException ex) {
                System.out.println(ex.getSQLState());
                if (ex.getSQLState().equals("23000")) {
                    //if queryid is already in the table. Do not reinsert. skip
                    continue;
                }
            }
            
        }

        DBMSConnection.safeClose(resultSet, stmt1, null);

        return queryObject;
    }

    /* Part of Backend engine
     * Clean all the tables which has the item similarity from previous runs
     *
     */
    public void clean(boolean all) throws Exception {
        /* CollabItems
         * ItemSimilarity
         * QryFragments
         * topkitemSimilarity
         * qryitems
         */
        if (mySqlConnection != null) {
            Statement stmt = mySqlConnection.createStatement();
            String strSQL1 = "DELETE FROM topkitemSimilarity";
            System.out.println("delete string:" + strSQL1);
            stmt.executeUpdate(strSQL1);

            strSQL1 = "DELETE FROM ItemSimilarity";
            System.out.println("delete string:" + strSQL1);
            stmt.executeUpdate(strSQL1);
            if (all) {




                strSQL1 = "DELETE FROM qryitems";
                System.out.println("delete string:" + strSQL1);
                stmt.executeUpdate(strSQL1);

                strSQL1 = "DELETE FROM CollabItems";
                System.out.println("delete string:" + strSQL1);
                stmt.executeUpdate(strSQL1);

                strSQL1 = "DELETE FROM QryFragments";
                System.out.println("delete string:" + strSQL1);
                stmt.executeUpdate(strSQL1);
            } else {
                QryParameters qParam = QryParameters.getInstance();
                String trainString = qParam.getValue("TRAINSESSION");
                strSQL1 = "DELETE FROM qryitems where queryid not in \n" +
                        "(select queryid from sessionqueries where sessionid in (" + trainString + "))";
                System.out.println("delete string:" + strSQL1);
                stmt.executeUpdate(strSQL1);

                strSQL1 = "DELETE FROM CollabItems where itemid not in \n" +
                        "(select itemid from qryitems)";
                System.out.println("delete string:" + strSQL1);
                stmt.executeUpdate(strSQL1);

                strSQL1 = "DELETE FROM QryFragments where queryid not in \n" +
                        "(select queryid from sessionqueries where sessionid in (" + trainString + "))";
                System.out.println("delete string:" + strSQL1);
                stmt.executeUpdate(strSQL1);
            }

            DBMSConnection.safeClose(null, stmt, null);
        }

    }

    public void close() throws Exception {
        mySqlConnection.close();
    }
}
